IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pOutProdDetail_Update'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pOutProdDetail_Update
GO

CREATE PROC dbo.pOutProdDetail_Update
	@outPaperDetailId	INT,
	@amount				INT,
	@returnValue		INT OUT
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/


/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT,
    @oldAmount		INT,
    @remainAmount	INT,
	@id				INT,
	@intProductId	INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/
	
	SELECT 
		@oldAmount = Amount,
		@intProductId = ProductId
	FROM OutProdDetail
	WHERE Id = @outPaperDetailId
	
	SET @remainAmount = (SELECT Product.Amount FROM Product WHERE Id = @intProductId)
	
	--IF @remainAmount > (@amount - @oldAmount) BEGIN
		BEGIN TRANSACTION
		BEGIN TRY			
			-- Update OutProdDetail
			UPDATE OutProdDetail
			SET Amount = @amount
			WHERE Id = @outPaperDetailId
			
			-- Update amount of old product
			UPDATE Product
			SET Amount = (Amount + @oldAmount - @amount)
			WHERE Id = @intProductId
			
			SET @intReturn = 0	
			COMMIT TRANSACTION
		END TRY
		BEGIN CATCH
			SET @intReturn = -2
			ROLLBACK TRANSACTION
		END CATCH
--	END
--	ELSE BEGIN
--		SET @intReturn = -1				
--	END
						
	SET @returnValue = @intReturn
							
  -- Return value 0 indicates success
  
lblExit:
	RETURN @intReturn
/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number

  -- By default, if there are any open transactions then roll them back
  IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN

  END

  -- If return value has not been explicitly set, then use the error
  -- number as return value to signify failure
  IF ((@intReturn IS NULL) OR (@intReturn = 0)) BEGIN
    SET @intReturn = @intErrNo

  END

 GOTO lblExit


